﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Honestysoft.BaseLibrary.Data;
using System.Data.Common;

namespace Honestysoft.Financing.DataAccess
{
    public class Reports_DAL : DatabaseHelper
    {
        public Reports_DAL() 
        { 
        }
        /// <summary>
        /// 构造函数，使用指定的连接字符串
        /// </summary>
        /// <param name="connectionStringName">连接字符串名称</param>
        public Reports_DAL(string connectionStringName)
            : base(connectionStringName)
        {

        }

        public DataTable GetAllCompany()
        {
            string sql = "Select ID,CompName From Org_Company Order by CompName";
            return ExecuteDataTable(sql);
        }

        /// <summary>
        /// 得到公司名称
        /// </summary>
        /// <param name="arrId"></param>
        /// <returns></returns>
        public DataTable GetCompanyName(string[] arrId)
        {
            string where = "";
            if (arrId.Length == 1)
            {
                where = "='" + arrId[0] + "'";
            }
            else
            {
                where = " in ('" + string.Join("','", arrId) + "')";
            }
            string sql = @"Select ID,CompName From Org_Company Where ID " + where;
            return ExecuteDataTable(sql);
        }

        public DataTable GetCompanyBankAccount()
        {
            string sql = @"Select A.*,B.BankName,B.BankAccount,B.IsDefault
	From Org_Company A Inner Join Org_BankAccount B On A.ID=B.CompanyID";
            return ExecuteDataTable(sql);
        }

        public string GetMaxCompanyCode()
        {
            string sql = "Select Max(CompCode) From Org_Company Where CompCode like 'GS%'";
            object obj = ExecuteScalar(sql);
            if (obj == null || obj == DBNull.Value)
            {
                return "GS-0000";
            }
            return obj.ToString();
        }
        /// <summary>
        ///  按月统计
        /// </summary>
        /// <returns></returns>
        public DataTable GetSummaryByMonth()
        {
            DbCommand command = GetStoredProcCommand("Proc_SummaryByMonth");
            return ExecuteDataTable(command);
        }
        /// <summary>
        /// 根据日期区间得到审核统计信息
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public DataTable GetSummaryByDate(DateTime startDate, DateTime endDate)
        {
            DbCommand command = GetStoredProcCommand("Proc_SummaryByDate");
            DbParameter pStartDate = CreateParameter("startDate");
            pStartDate.DbType = DbType.DateTime;
            pStartDate.Value = startDate;
            DbParameter pEndDate = CreateParameter("endDate");
            pEndDate.DbType = DbType.DateTime;
            pEndDate.Value = endDate;
            command.Parameters.Add(pStartDate);
            command.Parameters.Add(pEndDate);
            return ExecuteDataTable(command);
        }

        public string GetCompanyIdByName(string compName, DbTransaction transaction)
        {
            DbParameter parameter = CreateParameter("CompName");
            parameter.DbType = DbType.String;
            parameter.Value = compName;
            string sql = string.Format("Select ID From Org_Company Where CompName={0}", parameter.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(parameter);
            object obj = ExecuteScalar(command, transaction);
            if (obj == null || obj == DBNull.Value)
            {
                return "";
            }
            return obj.ToString();
        }
        /// <summary>
        /// 得到项目信息及产值（已赴现场考察）
        /// </summary>
        /// <param name="date"></param>
        /// <returns></returns>
        public DataTable GetR010_1(DateTime date)
        { 
            DbParameter pDate = CreateParameter("InvDate");
            pDate.DbType = DbType.DateTime;
            pDate.Value = date;
            string sql = string.Format(@"
                Select A.SN,A.Name,Convert(varchar(10),B.InvDate,120),Convert(varchar(10),A.PlanStart,120),Convert(varchar(10),A.PlanEnd,120),Convert(varchar(10),A.StartDate,120),Convert(varchar(10),A.EndDate,120),B.OutputValue
                From BD_Project A Inner Join (Select A.* From Bu_Investigate A Inner Join (
                Select Max(InvDate) InvDate,ProjectID
                From Bu_Investigate
                Where InvDate <= {0}
                Group by ProjectID) B On A.ProjectID=B.ProjectID And A.InvDate=B.InvDate) 
                B On A.ID=B.ProjectID
                Order by Convert(decimal(16,2),replace(replace(sn,'(','.'),')',''))", pDate.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(pDate);
            return ExecuteDataTable(command);
        }

        /// <summary>
        /// 得到部分项目工程建设计划进度示意图（已赴现场考察）
        /// </summary>
        /// <param name="date"></param>
        /// <returns></returns>
        public DataTable GetR010(DateTime date)
        {
            DbParameter pDate = CreateParameter("InvDate");
            pDate.DbType = DbType.DateTime;
            pDate.Value = date;
            string sql = string.Format(@"Select A.* From (
                Select convert(decimal(20,2),replace(replace(SN,'(','.'),')','')) as [序号],FCode+' '+Name as [项目名称],
                PlanStart as [开始日期],Datediff(day,PlanStart,PlanEnd) [计划完成时间],0 as [实际（预计）完成时间],0 as [类型]
                From BD_Project 
                Where ID in (Select ProjectID From Bu_Investigate Where InvDate <=  {0})
                union
                Select convert(decimal(20,2),replace(replace(SN,'(','.'),')','')),'',
                PlanEnd,0,Datediff(day,StartDate,EndDate) ,1
                From BD_Project 
                Where ID in (Select ProjectID From Bu_Investigate Where InvDate <=  {1})
                ) A
                Order by A.[序号],A.[类型]", pDate.ParameterName, pDate.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(pDate);
            return ExecuteDataTable(command);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="date"></param>
        /// <returns></returns>
        public DataTable GetR011(DateTime date)
        {
            DbParameter pDate = CreateParameter("date");
            pDate.DbType = DbType.DateTime;
            pDate.Value = date;
            DbCommand command = GetStoredProcCommand("Proc_GetR010_2");
            command.Parameters.Add(pDate);
            return ExecuteDataTable(command);
        }

        ///// <summary>
        ///// 得到项目投资计划完成情况及工程进度和资金拨付比例示意图
        ///// </summary>
        ///// <param name="date"></param>
        ///// <returns></returns>
        //public DataTable GetR011(DateTime date)
        //{
        //    DbParameter pDate = CreateParameter("date");
        //    pDate.DbType = DbType.DateTime;
        //    pDate.Value = date;
        //    DbCommand command = GetStoredProcCommand("Proc_GetJD");
        //    command.Parameters.Add(pDate);
        //    return ExecuteDataTable(command);
        //}
    }
}
